package com.example.skycode5.dao;

import com.example.skycode5.bean.NoticeBean;
import com.example.skycode5.util.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NoticeDao {
    DBConnection DBConn = new DBConnection();
    public List<NoticeBean> getList(int page){
        Connection conn = DBConn.getConn();
        String sql = "select * from notice limit "+(page-1)*10+",10";
        List<NoticeBean> list = new ArrayList<NoticeBean>();
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                NoticeBean noticeBean = new NoticeBean();
                noticeBean.setNoticeID(rs.getInt("NoticeID"));
                noticeBean.setNoticeTitle(rs.getString("NoticeTitle"));
                noticeBean.setNoticeContent(rs.getString("NoticeContent"));
                noticeBean.setNoticeTime(rs.getString("NoticeTime"));
                noticeBean.setNoticeTitle(rs.getString("NoticeTitle"));
                list.add(noticeBean);
            }
        } catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 添加新闻
     * @param notice
     * @return
     */
    public int add(NoticeBean notice) {
        Connection conn = DBConn.getConn();
        String sql = "insert into notice value(null,?,?,?,?)";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, notice.getNoticeTitle());
            pmst.setString(2, notice.getNoticeContent());
            pmst.setString(3, (new Date()).toString());
            pmst.setString(4, notice.getNoticeTitle());
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.fillInStackTrace() ;
        }
        return 0;
    }

    /**
     * 更新功能
     * @param notice
     * @return
     */
    public int update(NoticeBean notice) {
        Connection conn = DBConn.getConn();
        String sql = "update notice set noticeTitle=?,noticeContent=?,NoticeTime=?,noticeType=? where NoticeId=?";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, notice.getNoticeTitle());
            pmst.setString(2, notice.getNoticeContent());
            pmst.setString(3, (new Date()).toString());
            pmst.setString(4, notice.getNoticeTitle());
            pmst.setInt(5, notice.getNoticeID());
            return pmst.executeUpdate(sql);
        } catch (SQLException ex) {
            ex.fillInStackTrace() ;
        }
        return 0;
    }
    public int delete(int noticeId) {
        Connection conn = DBConn.getConn();
        try {
            String sql = "delete from notice where NoticeId=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1, noticeId);
            return pmst.executeUpdate(sql);
        }catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }
    public int count() {
        Connection conn = DBConn.getConn();
        try {
            String sql = "select count(*) as count from notice";
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}


